# coding: utf-8
import socket
import re
import os
import sys
import time
import csv
import gdata.spreadsheet.service

def get_sheet_key(client, book_name):
    m = re.search('http[s]*://', book_name)
    if (m is None):
        sheets = client.GetSpreadsheetsFeed()
        tlist = map(lambda e: e.title.text + " "  + e.id.text.rsplit('/', 1)[1], sheets.entry)
        for tt in tlist:
            buf = tt.rsplit(' ', 1)[0]
            key = tt.rsplit(' ', 1)[1]
            if buf == book_name:
                print 'Succeeded! key=', key
                return key
    print 'book ', book_name, ' is not found!'
    return 0

def do_insert(key, spr_client, infile):
    try:
        with open(infile) as f:
            reader = csv.reader(f, delimiter='\t')
            # build data
            rcnt=0
            for row in reader:
                arydata = {'date':row[0], 'server':row[1], 'area':row[2], 'alter':row[3], 'address':row[4], 'x':row[5], 'z':row[6], 'price':row[7], 'item':row[8]}
                # append to gspreadsheet
                entry = spr_client.InsertRow(arydata, key, wksht_id="od6")
                rcnt = rcnt + 1
                if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
                    print "Insert row succeeded (", rcnt, ")."
                else:
                    print "Insert row failed (", rcnt, ")!"
            f.close()
            os.remove(infile)
            return 0
    except IOError:
        print "Error opening file: ", infile
    except:
        print 'Error unhandled'
        return -1

HOST = '127.0.0.1'        # Local host
PORT = 50007              # Arbitrary port

is_exit = 0
spreadsheet_key = 0
spr_client = {}

while 1:
    s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    s.bind((HOST, PORT))
    s.listen(1)

    print 'Waiting for connection...'
    conn, addr = s.accept()

    print 'Connected by client', addr
    while 2:
        try:
            data = conn.recv(1024)
        except IOError, (errno, strerror):
            print "I/O error(%s): %s" % (errno, strerror)
            break
        except:
            print "Unexpected error:", sys.exc_info()[0]
            raise
            break

        if not data:
            print 'No data.'
            break

        data2 = re.sub('\\\\x00', '', repr(data))
        data2 = re.sub('\'', '', data2)
        data3 = data2.split("|")
        # パケットは2つ以上連結されてくる場合があるため、記号"|"で分割する。
        for elm in data3:
            m = re.search(r'^do_login', elm)
            if m is not None:
                print 'Trying login to google using account!'
                data4 = elm
                account_data = data4.split(' ')
                email = account_data[1]
                password = account_data[2]
                spr_client = gdata.spreadsheet.service.SpreadsheetsService()
                spr_client.email = email
                spr_client.password = password
                spr_client.source = 'Example Spreadsheet Writing Application'
                try:
                    spr_client.ProgrammaticLogin()
                    print "initialize cmpl."
                #except BadAuthentication:
                except:
                    print 'Authentication Failed!'
                    conn.send('BadAuthentication: Authentication Failed!')
                    break

            m = re.search(r'^open_sheet', elm)
            if m is not None:
                m = re.search('http[s]*://', elm)
                if m is None:
                    book_name = elm.split(' ')[1]
                    print 'open_sheet!'
                    spreadsheet_key = get_sheet_key(spr_client, book_name)
                else:
                    # only parse key from URL
                    url = elm.split(' ')[1]
                    param1 = url.split('=')[1]
                    spreadsheet_key = param1.split('&')[0]
                print 'key is: ', spreadsheet_key
            m = re.search(r'^insert', elm)
            if m is not None:
                if spreadsheet_key is not 0:
                    infile = elm.split(' ')[1]
                    print 'Insert from file: ', infile
                    res = do_insert(spreadsheet_key, spr_client, infile)
                    if (res == -1):
                        is_exit = 1
                        break
                    print 'Insert from file: ', infile, ' done.'
                else:
                    print 'Error! Initialization is not complete.'

            m = re.search('^py_exit', elm)
            if m is not None:
                is_exit = 1
                break
        if (is_exit):
            break
        conn.send(data)
    # while 2

    conn.close()
    print 'Server closed.'
    if (is_exit):
        break
# while 1

print 'Exiting.'
